﻿Imports DTO
Imports System.Data.OleDb
Public Class SoTietKiemDAO
    Public Function ThemSoTietKiem(ByVal stkDTO As SoTietKiemDTO) As Integer
        Dim sql As String = "Insert Into SOTIETKIEM (MaLoaiTietKiem,MaKhachHang,SoTienGoi,NgayMoSo,SoDu,MaTinhTrangSoTietKiem) Values(@MaLoaiTietKiem,@MaKhachHang,@SoTienGoi,@NgayMoSo,@SoDu,@MaTinhTrangSoTietKiem)"
        Dim coon As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, coon)
        Dim para As OleDbParameter

        para = cmd.Parameters.Add("@MaLoaiTietKiem", OleDbType.Integer)
        para.Value = stkDTO.MaLoaiTietKiem

        para = cmd.Parameters.Add("@MaKhachHang", OleDbType.Integer)
        para.Value = stkDTO.MaKH

        para = cmd.Parameters.Add("@SoTienGoi", OleDbType.Double)
        para.Value = stkDTO.SoTienGoi

        para = cmd.Parameters.Add("@NgayMoSo", OleDbType.DBDate)
        para.Value = stkDTO.NgayMoSo.Date

        para = cmd.Parameters.Add("@SoDu", OleDbType.Double)
        para.Value = stkDTO.SoDu

        para = cmd.Parameters.Add("@MaTinhTrangSoTietKiem", OleDbType.Integer)
        para.Value = stkDTO.MaTinhTrangSoTietKiem

        Dim i As Integer = cmd.ExecuteNonQuery()
        coon.Close()
        If i >= 0 Then
            Return 1
        Else
            Return 0
        End If
    End Function
    ' lấy tất cả các sổ tiết kiệm
    Public Function LaySoTietKiem() As List(Of SoTietKiemDTO)
        Dim sql As String = "Select * From SoTietKiem Where MaTinhTrangSoTietKiem=1"
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim ds As New List(Of SoTietKiemDTO)()
        While (reader.Read())
            Dim stkDTO As New SoTietKiemDTO()
            stkDTO.MaSo = reader.GetInt32(0)
            stkDTO.MaLoaiTietKiem = reader.GetInt32(1)
            stkDTO.MaKH = reader.GetInt32(2)
            stkDTO.NgayMoSo = reader.GetDateTime(3)
            stkDTO.SoTienGoi = reader.GetDouble(4)
            stkDTO.SoDu = reader.GetDouble(5)
            stkDTO.MaTinhTrangSoTietKiem = reader.GetInt32(6)
            ds.Add(stkDTO)
        End While
        con.Close()
        Return ds
    End Function
    'Lấy sổ tiết kiệm được gửi tiền
    Public Function LaySoTietKiemDuocGoiTien() As List(Of SoTietKiemDTO)
        Dim tsDAO As New ThamSoDAO()
        Dim tsDTO As New ThamSoDTO()
        tsDTO = tsDAO.LayBangThamSo()
        Dim sql As String = "Select * From SoTietKiem Where MaTinhTrangSoTietKiem=1 And  MaLoaiTietKiem=" + tsDTO.MaLoaiTietKiemDuocGoiTien.ToString()
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim ds As New List(Of SoTietKiemDTO)()
        While (reader.Read())
            Dim stkDTO As New SoTietKiemDTO()
            stkDTO.MaSo = reader.GetInt32(0)
            stkDTO.StrMaSo = reader.GetInt32(0).ToString()
            stkDTO.MaLoaiTietKiem = reader.GetInt32(1)
            stkDTO.MaKH = reader.GetInt32(2)
            stkDTO.NgayMoSo = reader.GetDateTime(3)
            stkDTO.SoTienGoi = reader.GetDouble(4)
            stkDTO.SoDu = reader.GetDouble(5)
            stkDTO.MaTinhTrangSoTietKiem = reader.GetInt32(6)

            ds.Add(stkDTO)
        End While
        con.Close()
        Return ds
    End Function
    'Lấy số dư tài khoản 
    Public Function LaySoDuTaiKhoan(ByVal maso As Integer) As Double
        Dim sql As String = "Select * From SoTietKiem Where MaSo=" + maso.ToString()
        sql += " And MaTinhTrangSoTietKiem=1"
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim kq As Double = 0
        While (reader.Read())
            kq = reader.GetDouble(5)
        End While
        con.Close()
        Return kq
    End Function

    'Update lại số dư tài khoản sau khi Goi tiền
    Public Function CapNhatSoDuTaiKhoanSauKhiGoi(ByVal maso As Integer, ByVal sotiengoi As Double) As Integer
        Dim sql As String = "Update SoTietKiem Set SoDu=SoDu+@sotiengoi Where MaSo=" + maso.ToString()
        Dim coon As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, coon)
        Dim para As OleDbParameter
        para = cmd.Parameters.Add("@sotiengoi", OleDbType.Double)
        para.Value = sotiengoi

        Dim kq As Integer = cmd.ExecuteNonQuery()
        coon.Close()
        If kq >= 0 Then
            Return 1
        Else
            Return 0
        End If
    End Function
    'Update lại số dư tài khoản sau khi Rut tiền
    Public Function CapNhatSoDuTaiKhoanSauKhiRut(ByVal maso As Integer, ByVal sotienrut As Double) As Integer
        Dim sql As String = "Update SoTietKiem Set SoDu=SoDu-@sotienrut Where MaSo=" + maso.ToString()
        Dim coon As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, coon)
        Dim para As OleDbParameter
        para = cmd.Parameters.Add("@sotienrut", OleDbType.Double)
        para.Value = sotienrut

        Dim kq As Integer = cmd.ExecuteNonQuery()
        coon.Close()
        If kq >= 0 Then
            Return 1
        Else
            Return 0
        End If
    End Function
    'Tìm kiếm theo mã sổ
    Public Function TimkiemTheoMaSo(ByVal maso As Integer) As SoTietKiemDTO
        Dim sql As String = "Select * From SoTietKiem Where MaSo=" + maso.ToString()
        sql += " And MaTinhTrangSoTietKiem=1"
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim stkDTO As New SoTietKiemDTO()
        While (reader.Read())
            stkDTO.MaSo = reader.GetInt32(0)
            stkDTO.MaLoaiTietKiem = reader.GetInt32(1)
            stkDTO.MaKH = reader.GetInt32(2)
            stkDTO.NgayMoSo = reader.GetDateTime(3)
            stkDTO.SoTienGoi = reader.GetDouble(4)
            stkDTO.SoDu = reader.GetDouble(5)
            stkDTO.MaTinhTrangSoTietKiem = reader.GetInt32(6)
        End While
        con.Close()
        Return stkDTO
    End Function
    'Tìm kiếm theo nhiều tiêu trí
    Public Function TimKiem(ByVal stkCrt As SoTietKiemCrt) As DataTable

        Dim con As OleDbConnection
        con = DataProvider.ConnectDB()
        Dim cmd As OleDbDataAdapter = BuildQuery(stkCrt, con)
        Dim adapter As OleDbDataAdapter = cmd
        Dim dsstk As New DataTable()
        adapter.Fill(dsstk)
        'While (reader.Read())
        'Dim _stkCrt As New SoTietKiemCrt()
        ' _stkCrt.MaSo = reader.GetInt32(0)
        ' _stkCrt.MaLoaiTietKiem = reader.GetInt32(1)
        ' _stkCrt.TenKhachHang = reader.GetString(2)
        ' _stkCrt.NgayMoSo = reader.GetDateTime(3)
        ' _stkCrt.SoDu = reader.GetDouble(4)
        ' dsstk.Add(_stkCrt)
        ' End While
        con.Close()
        Return dsstk
    End Function

    Private Function BuildQuery(ByVal stkCrt As SoTietKiemCrt, ByVal cn As OleDbConnection) As OleDbDataAdapter


        Dim cmd As New OleDbCommand()
        Dim strDKMaSoTietKiem = " 1=1 "
        Dim strDKMaLoaiTietKiem = " 1=1 "
        Dim strDKTenKhachHang = " 1=1 "
        Dim strDKNgayMoSo = " 1=1 "
        Dim strDKSoDu = " 1=1 "

        If (stkCrt.MaSo <> 0) Then
            strDKMaSoTietKiem = " STK.MaSo = ? "
            cmd.Parameters.Add("@MaSo", OleDbType.Integer)
            cmd.Parameters("@MaSo").Value = stkCrt.MaSo
        End If
        If (stkCrt.ChkLoaiTietKiem) Then
            strDKMaLoaiTietKiem = " STK.MaLoaiTietKiem=? "
            cmd.Parameters.Add("@MaLTK", OleDbType.Integer)
            cmd.Parameters("@MaLTK").Value = stkCrt.MaLoaiTietKiem
        End If
        If (stkCrt.ChkKhachHang) Then
            strDKTenKhachHang = " KH.TenKhachHang like ?"
            cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
            cmd.Parameters("@TenKhachHang").Value = "%" + stkCrt.TenKhachHang + "%"
        End If
        If (stkCrt.ChkNgayMoSo) Then
            strDKNgayMoSo = " STK.NgayMoSo between ? and ? "
            cmd.Parameters.Add("@NgayMoSoTu", OleDbType.Date)
            cmd.Parameters.Add("@NgayMoSoDen", OleDbType.Date)
            cmd.Parameters("@NgayMoSoTu").Value = stkCrt.NgayMoSoTu
            cmd.Parameters("@NgayMoSoDen").Value = stkCrt.NgayMoSoDen
        End If
        If (stkCrt.ChkSoDu) Then
            strDKSoDu = " STK.SoDu between ? and ?"
            cmd.Parameters.Add("@SoDuTu", OleDbType.Double)
            cmd.Parameters.Add("@SoDuDen", OleDbType.Double)
            cmd.Parameters("@SoDuTu").Value = stkCrt.SoDuTu
            cmd.Parameters("@SoDuDen").Value = stkCrt.SoDuDen
        End If



        Dim strDKWhere As String = " Where "
        strDKWhere += strDKMaSoTietKiem
        strDKWhere += " and " + strDKMaLoaiTietKiem
        strDKWhere += " and " + strDKTenKhachHang
        strDKWhere += " and " + strDKSoDu
        strDKWhere += " and " + strDKNgayMoSo
        strDKWhere += " and STK.MaKhachHang=KH.MaKhachHang And STK.MaTinhTrangSoTietKiem=1 And LTK.MaLoaiTietKiem = STK.MaLoaiTietKiem "

        Dim strSQL As String = "Select STK.MaSo,LTK.TenLoaiTietKiem,KH.TenKhachHang,STK.NgayMoSo,STK.SoDu From SoTietKiem STK, KhachHang KH,LoaiTietKiem LTK "
        strSQL += strDKWhere
        strSQL += "Group By STK.MaSo,LTK.TenLoaiTietKiem,KH.TenKhachHang,STK.NgayMoSo,STK.SoDu"
        strSQL += " Order by KH.TenKhachHang "


        cmd.Connection = cn
        cmd.CommandText = strSQL
        Dim adapter As New OleDbDataAdapter(cmd)
        Return adapter
    End Function

    Public Function TraCuuSo(ByVal sTietKiemCrt As SoTietKiemCrt) As DataTable
        Dim dtDanhSach As New DataTable()
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT MaSo,TenLoaiTietKiem,TenKhachHang,NgayMoSo,SoDu FROM SoTietKiem stk,LoaiTietKiem ml,KhachHang kh"
        strSQL += " WHERE stk.MaLoaiTietKiem = ml.MaLoaiTietKiem And stk.MaKhachHang = kh.MaKhachHang"
        If sTietKiemCrt.MaSo <> 0 Then
            strSQL += " And stk.MaSo = " + sTietKiemCrt.MaSo.ToString()
        End If
        If sTietKiemCrt.ChkKhachHang = True Then
            strSQL += " And kh.TenKhachHang Like '%" + sTietKiemCrt.TenKhachHang.ToString() + "%'"
        End If
        If sTietKiemCrt.ChkLoaiTietKiem = True Then
            strSQL += " And ml.MaLoaiTietKiem = " + sTietKiemCrt.MaLoaiTietKiem.ToString()
        End If
        If sTietKiemCrt.ChkSoDu = True Then
            strSQL += " And stk.SoDu Between " + sTietKiemCrt.SoDuTu.ToString() + " and " + sTietKiemCrt.SoDuDen.ToString()
        End If
        If sTietKiemCrt.ChkNgayMoSo = True Then
            strSQL += " And stk.NgayMoSo Between " + sTietKiemCrt.NgayMoSoTu.Date + " and " + sTietKiemCrt.NgayMoSoDen.Date
        End If
        strSQL += " Group By MaSo,TenLoaiTietKiem,TenKhachHang,NgayMoSo,SoDu"
        Dim comand As New OleDbCommand(strSQL, cnn)
        Dim Adapter As New OleDbDataAdapter()
        Adapter.SelectCommand = comand
        Adapter.Fill(dtDanhSach)
        cnn.Close()
        Return dtDanhSach
    End Function
    'Lấy mã tiết kiệm max
    Public Function LayMaTietKiemMax() As Integer
        Dim sql As String = "Select Max(MaSo) From SoTietKiem"
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim maso As Integer = cmd.ExecuteScalar
        con.Close()
        Return maso
    End Function

    Public Function LayThongTinSoTietKiem(ByVal maso As String) As DataTable
        Dim dt As New DataTable()
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT ltk.TenLoaiTietKiem,stk.NgayMoSo,stk.SoTienGoi,kh.TenKhachHang,kh.DiaChi,kh.CMND FROM SOTIETKIEM stk,KHACHHANG kh,LOAITIETKIEM ltk WHERE stk.MaLoaiTietKiem = ltk.MaLoaiTietKiem And stk.MaKhachHang = kh.MaKhachHang And stk.MaSo = " + maso
        strSQL += " And stk.MaTinhTrangSoTietKiem=1"
        Dim comand As New OleDbCommand(strSQL, cnn)
        Dim Adapter As New OleDbDataAdapter()
        Adapter.SelectCommand = comand
        Adapter.Fill(dt)
        cnn.Close()
        Return dt
    End Function

    Public Function LayThongTinDanhSachSoTietKiem() As DataTable
        Dim dtDanhSach As New DataTable()
        Dim cnn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT stk.MaSo,ltk.TenLoaiTietKiem,ltk.MaLoaiTietKiem,kh.TenKhachHang,kh.MaKhachHang,stk.SoTienGoi,stk.NgayMoSo,stk.SoDu,kh.CMND,kh.DiaChi"
        strSQL += " FROM SOTIETKIEM stk,KHACHHANG kh,LOAITIETKIEM ltk "
        strSQL += " WHERE stk.MaLoaiTietKiem = ltk.MaLoaiTietKiem And stk.MaKhachHang = kh.MaKhachHang And stk.MaTinhTrangSoTietKiem=1"
        Dim comand As New OleDbCommand(strSQL, cnn)
        Dim Adapter As New OleDbDataAdapter()
        Adapter.SelectCommand = comand
        Adapter.Fill(dtDanhSach)
        cnn.Close()
        Return dtDanhSach
    End Function
    'Hàm cập nhập sổ tiết kiệm
    Public Function CapNhatSoTietKiem(ByVal stkDTO As SoTietKiemDTO) As Integer
        Dim sql As String = "Update SOTIETKIEM Set MaLoaiTietKiem=?,SoTienGoi=?,NgayMoSo=?,SoDu=? Where MaSo=" + stkDTO.MaSo.ToString()
        Dim coon As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(Sql, coon)
        Dim para As OleDbParameter

        para = cmd.Parameters.Add("@MaLoaiTietKiem", OleDbType.Integer)
        para.Value = stkDTO.MaLoaiTietKiem

        para = cmd.Parameters.Add("@SoTienGoi", OleDbType.Double)
        para.Value = stkDTO.SoTienGoi

        para = cmd.Parameters.Add("@NgayMoSo", OleDbType.DBDate)
        para.Value = stkDTO.NgayMoSo

        para = cmd.Parameters.Add("@SoDu", OleDbType.Double)
        para.Value = stkDTO.SoDu

        Dim i As Integer = cmd.ExecuteNonQuery()
        coon.Close()
        If i > 0 Then
            Return 1
        Else
            Return 0
        End If
    End Function

    Public Function ImportTuExcel(ByVal str As String) As DataTable
        Dim dt As New DataTable()
        Dim cnn As New OleDbConnection
        cnn = DataProvider.KetNoiExcel(str)
        Dim _command As OleDbCommand = New OleDbCommand()
        _command.Connection = cnn
        _command.CommandText = "select * from [Sheet1$]"
        Dim adapter As New OleDbDataAdapter(_command)
        Try
            adapter.Fill(dt)
        Catch ex As Exception

        End Try
        cnn.Close()
        Return dt
    End Function

    Public Function TongThu(ByVal _date As Date) As DataTable
        Dim dt As New DataTable()
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim comand As New OleDbCommand()
        Dim strSQL = "select ltk.MaLoaiTietKiem,Sum(stk.Sotiengoi) as TongThu From SoTietKiem stk,LoaiTietKiem ltk"
        strSQL += " Where stk.NgayMoSo=@ngay"
        strSQL += " And stk.MaLoaiTietKiem = ltk.MaLoaiTietKiem And ltk.MaTinhTrangLoaiTietKiem = 1"
        strSQL += " Group By ltk.MaLoaiTietKiem"
        comand.Connection = cnn
        comand.CommandText = strSQL
        Dim para As OleDbParameter
        para = comand.Parameters.Add("@ngay", OleDbType.DBDate)
        para.Value = _date.Date
        Dim adapter As New OleDbDataAdapter(comand)
        adapter.Fill(dt)
        cnn.Close()
        Return dt
    End Function
    'Hàm cập nhật lại mã tình trạng sổ tiết kiệm
    Public Function CapNhaMTTSTK(ByVal matt As Integer, ByVal ngayDong As Date, ByVal maso As Integer) As Integer
        ngayDong = Date.Now.Date
        Dim sql As String = "Update SOTIETKIEM Set MaTinhTrangSoTietKiem=" + matt.ToString()
        sql += ",NgayMoSo=@ngay Where MaSo=" + maso.ToString()
        Dim coon As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, coon)
        Dim para As OleDbParameter

        para = cmd.Parameters.Add("@ngay", OleDbType.DBDate)
        para.Value = ngayDong.Date
        Dim i As Integer = cmd.ExecuteNonQuery()
        coon.Close()
        If i > 0 Then
            Return 1
        Else
            Return 0
        End If
    End Function
    'Số lượng sổ mở hang tháng
    Public Function SoMo(ByVal ngayMo As Date, ByVal maltk As Integer) As Long
        Dim sql As String = "Select Sum(MaTinhTrangSoTietKiem) From SoTietKiem "
        sql += "Where NgayMoSo=@ngayMo And MaTinhTrangSoTietKiem=1 And MaLoaiTietKiem=" + maltk.ToString()
        Dim coon As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, coon)
        Dim para As OleDbParameter

        para = cmd.Parameters.Add("@ngayMo", OleDbType.DBDate)
        para.Value = ngayMo.Date

        Dim kq As Long = 0
        If cmd.ExecuteScalar().ToString() <> "" Then
            kq = cmd.ExecuteScalar()
        End If
        coon.Close()
        Return kq
    End Function
    'Số lượng sổ đóng hàng tháng
    Public Function SoDong(ByVal ngayDong As Date, ByVal maltk As Integer) As Long
        Dim sql As String = "Select Sum(MaTinhTrangSoTietKiem) From SoTietKiem "
        sql += "Where NgayMoSo=@ngayDong And MaTinhTrangSoTietKiem=2 And MaLoaiTietKiem=" + maltk.ToString()
        Dim coon As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, coon)
        Dim para As OleDbParameter

        para = cmd.Parameters.Add("@ngayDong", OleDbType.DBDate)
        para.Value = ngayDong.Date

        Dim kq As Long = 0
        If cmd.ExecuteScalar().ToString() <> "" Then
            kq = cmd.ExecuteScalar()
        End If

        coon.Close()
        Return kq
    End Function
    ' Báo cáo sổ mở đóng hàng tháng
    Public Function SoMoDong(ByVal ngay As Date, ByVal maltk As Integer) As DataTable
        Dim dt As New DataTable()
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim str = "Select Distinct STK.NgayMoSo,LTK.TenLoaiTietKiem From SoTietKiem STK,LoaiTietKiem LTK "
        str += "Where Month(STK.NgayMoSo)=@Thang And Year(STK.NgayMoSo)=@Nam And STK.MaLoaiTietKiem=" + maltk.ToString()
        str += " And STK.MaLoaiTietKiem=LTK.MaLoaiTietKiem"
        str += " Group By STK.NgayMoSo,LTK.TenLoaiTietKiem"
        Dim cmd As New OleDbCommand(str, cnn)
        Dim para As OleDbParameter

        para = cmd.Parameters.Add("@Thang", OleDbType.Integer)
        para.Value = ngay.Month

        para = cmd.Parameters.Add("@Nam", OleDbType.Integer)
        para.Value = ngay.Year
        Dim Adapter As New OleDbDataAdapter(cmd)
        Adapter.Fill(dt)
        Dim cl2 As DataColumn = dt.Columns.Add("SoMo", Type.GetType("System.Int32"))
        Dim cl3 As DataColumn = dt.Columns.Add("SoDong", Type.GetType("System.Int32"))
        Dim cl4 As DataColumn = dt.Columns.Add("ChenhLech", Type.GetType("System.Int32"))
        Dim cl5 As DataColumn = dt.Columns.Add("Thang", Type.GetType("System.String"))
        Dim i As Integer
        For i = 0 To dt.Rows.Count - 1

            Dim _somo As Long = SoMo(dt.Rows(i)(0), maltk)
            Dim _sodong As Long = SoDong(dt.Rows(i)(0), maltk)
            Dim _chenhlech As Long = Math.Abs(_somo - _sodong)

            dt.Rows(i)(2) = _somo
            dt.Rows(i)(3) = _sodong
            dt.Rows(i)(4) = _chenhlech
            dt.Rows(i)(5) = ngay.Month.ToString() + "/" + ngay.Year.ToString()
        Next
        cnn.Close()
        Return dt
    End Function

    Public Function Xoa(ByVal maso As Integer) As Integer
        Dim kq As Integer
        Dim cnn As New OleDbConnection
        cnn = DataProvider.ConnectDB()
        Dim strSQL As String = "UPDATE SOTIETKIEM SET MaTinhTrangSOTietKiem = 2 WHERE maso = " + maso.ToString()
        Dim comand As New OleDbCommand(strSQL, cnn)
        kq = comand.ExecuteNonQuery()
        cnn.Close()
        Return kq
    End Function
End Class
